﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;

namespace syc.Produce
{
    public partial class 生产进度日报表 : System.Web.UI.Page
    {
        protected HSSFWorkbook hssfworkbook;

        protected double[] jindu1 = new double[26];
        protected double[] jindu2 = new double[48];
        protected int[] jindu3 = new int[66];
        protected int[] jindu4 = new int[15];
        protected double[] jindu5 = new double[26];

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!this.IsPostBack)
            {
                RiQi.Text = DateTime.Today.AddDays(-1).ToString("yyyy年M月d日");
            }
            this.QueryData();
        }

        protected void InitializeWorkbook()
        {

            FileStream file = new FileStream(HttpContext.Current.Server.MapPath("模板/生产进度日报表.xls"), FileMode.Open, FileAccess.Read);

            hssfworkbook = new HSSFWorkbook(file);

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "陕西延长石油材料有限公司";
            hssfworkbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "报表";
            si.Title = "二分厂套管管体日生产报表";
            hssfworkbook.SummaryInformation = si;
        }

        protected void WriteToFile()
        {
            DateTime d;
            if (!DateTime.TryParse(this.RiQi.Text, out d))
            {
                d = DateTime.Today;
            }
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + Server.UrlEncode("生产进度日报表[" + d.ToLongDateString()) + "].xls\"");
            hssfworkbook.Write(Response.OutputStream);
        }


        private void QueryData()
        {
            DateTime d1, d2, d3;
            string ds1, ds2, ds3;
            int i;
            if (!DateTime.TryParse(RiQi.Text, out d1))
            {
                return;
            }
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            string sql=
@"declare @y int
declare @d1 smalldatetime
if exists(select * from 生产月设置 where 分厂=@fc and @d0>=起始日期 and @d0<=截止日期)
begin
	select @y=年度,@d1=起始日期 from 生产月设置 where 分厂=@fc and @d0>=起始日期 and @d0<=截止日期
	if exists(select * from 生产月设置 where 分厂=@fc and 年度=@y and 月份=1)
	begin
		select @d1 as 月累计日期,起始日期 as 年累计日期 from 生产月设置 where 分厂=@fc and 年度=@y and 月份=1
	end
end";//分厂每月扎帐日期设置查询语句


            //一分厂日进度
            Cmd.CommandText = sql.Replace("@d0", "'" + d1.ToShortDateString() + "'");
            Cmd.CommandText = Cmd.CommandText.Replace("@d0", "'" + d1.ToShortDateString() + "'");
            Cmd.CommandText = Cmd.CommandText.Replace("@fc", "1");
            Cmd.CommandType = CommandType.Text;
            SqlDataReader Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                d2 = (DateTime)Reader["月累计日期"];
                d3 = (DateTime)Reader["年累计日期"];
            }
            else
            {
                d2 = DateTime.Parse(d1.Year.ToString() + "年" + d1.Month.ToString() + "月1日");
                d3 = DateTime.Parse(d1.Year.ToString() + "年1月1日");
            }
            Reader.Close();
            ds1 = "'" + d1.ToShortDateString() + "'";
            ds2 = "'" + d2.ToShortDateString() + "'";
            ds3 = "'" + d3.ToShortDateString() + "'";

            Cmd.CommandText =
@"select * from
(select
ISNULL(SUM(车丝1),0) as 车丝1,
ISNULL(SUM(车丝2),0) as 车丝2,
ISNULL(SUM(车丝3),0) as 车丝3,
ISNULL(SUM(车丝4),0) as 车丝4
from 生产进度 where 分厂ID=1 and 日期=@d1
) t1
,
(select
ISNULL(SUM(车丝1),0) as 车丝1累计,
ISNULL(SUM(车丝2),0) as 车丝2累计,
ISNULL(SUM(车丝3),0) as 车丝3累计,
ISNULL(SUM(车丝4),0) as 车丝4累计
from 生产进度 where 分厂ID=1 and 日期>=@d2 and 日期<=@d1
) t2
,
(select
ISNULL(SUM(称测支数),0) as 套管称重测长支,
ISNULL(SUM(包装捆数),0) as 套管精整捆,
ISNULL(SUM(入库捆数),0) as 套管入库捆,
ISNULL(SUM(称测重量),0) as 套管称重测长kg,
ISNULL(SUM(包装重量),0) as 套管精整kg,
ISNULL(SUM(入库重量),0) as 套管入库kg
from 生产进度 where 分厂ID=1 and 产品类别ID=2 and 日期=@d1
) t3
,
(select
ISNULL(SUM(称测支数),0) as 套管称重测长累计支,
ISNULL(SUM(包装捆数),0) as 套管精整累计捆,
ISNULL(SUM(入库捆数),0) as 套管入库累计捆,
ISNULL(SUM(称测重量),0) as 套管称重测长累计kg,
ISNULL(SUM(包装重量),0) as 套管精整累计kg,
ISNULL(SUM(入库重量),0) as 套管入库累计kg
from 生产进度 where 分厂ID=1 and 产品类别ID=2 and 日期>=@d2 and 日期<=@d1
) t4
,
(select
ISNULL(SUM(称测支数),0) as 套管称重测长年累计支,
ISNULL(SUM(包装捆数),0) as 套管精整年累计捆,
ISNULL(SUM(入库捆数),0) as 套管入库年累计捆,
ISNULL(SUM(称测重量),0) as 套管称重测长年累计kg,
ISNULL(SUM(包装重量),0) as 套管精整年累计kg,
ISNULL(SUM(入库重量),0) as 套管入库年累计kg
from 生产进度 where 分厂ID=1 and 产品类别ID=2 and 日期>=@d3 and 日期<=@d1
) t5";
            Cmd.CommandText = Cmd.CommandText.Replace("@d1", ds1);
            Cmd.CommandText = Cmd.CommandText.Replace("@d2", ds2);
            Cmd.CommandText = Cmd.CommandText.Replace("@d3", ds3);

            Cmd.CommandType = CommandType.Text;
            Reader = Cmd.ExecuteReader();
            //Response.Write(ds1 + " " + ds2 + " " + ds3 + " ");
            //Response.Write(Cmd.CommandText);
            if (Reader.Read())
            {
                for (i = 0; i < Reader.FieldCount; i++)
                {
                    if (Reader[i].GetType().Equals(i.GetType()))
                    {
                        jindu1[i] = (int)(Reader[i]);
                    }
                    else
                    {
                        jindu1[i] = (double)(Reader[i]);
                    }
                }
            }
            Reader.Close();

            //二分厂日进度
            Cmd.CommandText = sql.Replace("@d0", "'" + d1.ToShortDateString() + "'");
            Cmd.CommandText = Cmd.CommandText.Replace("@d0", "'" + d1.ToShortDateString() + "'");
            Cmd.CommandText = Cmd.CommandText.Replace("@fc", "2");
            Cmd.CommandType = CommandType.Text;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                d2 = (DateTime)Reader["月累计日期"];
                d3 = (DateTime)Reader["年累计日期"];
            }
            else
            {
                d2 = DateTime.Parse(d1.Year.ToString() + "年" + d1.Month.ToString() + "月1日");
                d3 = DateTime.Parse(d1.Year.ToString() + "年1月1日");
            }
            Reader.Close();
            ds1 = "'" + d1.ToShortDateString() + "'";
            ds2 = "'" + d2.ToShortDateString() + "'";
            ds3 = "'" + d3.ToShortDateString() + "'";

            Cmd.CommandText =
@"select * from
(select
ISNULL(SUM(车丝1),0) as 车丝1,
ISNULL(SUM(车丝2),0) as 车丝2,
ISNULL(SUM(车丝3),0) as 车丝3,
ISNULL(SUM(车丝4),0) as 车丝4,
ISNULL(SUM(车丝5),0) as 车丝5,
ISNULL(SUM(车丝6),0) as 车丝6
from 生产进度 where 分厂ID=2 and 日期=@d1
) t1
,
(select
ISNULL(SUM(车丝1),0) as 车丝1累计,
ISNULL(SUM(车丝2),0) as 车丝2累计,
ISNULL(SUM(车丝3),0) as 车丝3累计,
ISNULL(SUM(车丝4),0) as 车丝4累计,
ISNULL(SUM(车丝5),0) as 车丝5累计,
ISNULL(SUM(车丝6),0) as 车丝6累计
from 生产进度 where 分厂ID=2 and 日期>=@d2 and 日期<=@d1
) t2
,
(select
ISNULL(SUM(称测支数),0) as 套管称重测长支,
ISNULL(SUM(包装捆数),0) as 套管精整捆,
ISNULL(SUM(入库捆数),0) as 套管入库捆,
ISNULL(SUM(称测重量),0) as 套管称重测长kg,
ISNULL(SUM(包装重量),0) as 套管精整kg,
ISNULL(SUM(入库重量),0) as 套管入库kg
from 生产进度 where 分厂ID=2 and 产品类别ID=2 and 日期=@d1
) t3
,
(select
ISNULL(SUM(称测支数),0) as 套管称重测长累计支,
ISNULL(SUM(包装捆数),0) as 套管精整累计捆,
ISNULL(SUM(入库捆数),0) as 套管入库累计捆,
ISNULL(SUM(称测重量),0) as 套管称重测长累计kg,
ISNULL(SUM(包装重量),0) as 套管精整累计kg,
ISNULL(SUM(入库重量),0) as 套管入库累计kg
from 生产进度 where 分厂ID=2 and 产品类别ID=2 and 日期>=@d2 and 日期<=@d1
) t4
,
(select
ISNULL(SUM(称测支数),0) as 套管称重测长年累计支,
ISNULL(SUM(包装捆数),0) as 套管精整年累计捆,
ISNULL(SUM(入库捆数),0) as 套管入库年累计捆,
ISNULL(SUM(称测重量),0) as 套管称重测长年累计kg,
ISNULL(SUM(包装重量),0) as 套管精整年累计kg,
ISNULL(SUM(入库重量),0) as 套管入库年累计kg
from 生产进度 where 分厂ID=2 and 产品类别ID=2 and 日期>=@d3 and 日期<=@d1
) t5
,
(select
ISNULL(SUM(称测支数),0) as 油管称重测长支,
ISNULL(SUM(包装捆数),0) as 油管精整捆,
ISNULL(SUM(入库捆数),0) as 油管入库捆,
ISNULL(SUM(称测重量),0) as 油管称重测长kg,
ISNULL(SUM(包装重量),0) as 油管精整kg,
ISNULL(SUM(入库重量),0) as 油管入库kg
from 生产进度 where 分厂ID=2 and 产品类别ID=1 and 日期=@d1
) t6
,
(select
ISNULL(SUM(称测支数),0) as 油管称重测长累计支,
ISNULL(SUM(包装捆数),0) as 油管精整累计捆,
ISNULL(SUM(入库捆数),0) as 油管入库累计捆,
ISNULL(SUM(称测重量),0) as 油管称重测长累计kg,
ISNULL(SUM(包装重量),0) as 油管精整累计kg,
ISNULL(SUM(入库重量),0) as 油管入库累计kg
from 生产进度 where 分厂ID=2 and 产品类别ID=1 and 日期>=@d2 and 日期<=@d1
) t7
,
(select
ISNULL(SUM(称测支数),0) as 油管称重测长年累计支,
ISNULL(SUM(包装捆数),0) as 油管精整年累计捆,
ISNULL(SUM(入库捆数),0) as 油管入库年累计捆,
ISNULL(SUM(称测重量),0) as 油管称重测长年累计kg,
ISNULL(SUM(包装重量),0) as 油管精整年累计kg,
ISNULL(SUM(入库重量),0) as 油管入库年累计kg
from 生产进度 where 分厂ID=2 and 产品类别ID=1 and 日期>=@d3 and 日期<=@d1
) t8
";
            Cmd.CommandText = Cmd.CommandText.Replace("@d1", ds1);
            Cmd.CommandText = Cmd.CommandText.Replace("@d2", ds2);
            Cmd.CommandText = Cmd.CommandText.Replace("@d3", ds3);

            Cmd.CommandType = CommandType.Text;
            Reader = Cmd.ExecuteReader();

            if (Reader.Read())
            {
                for (i = 0; i < Reader.FieldCount; i++)
                {
                    if (Reader[i].GetType().Equals(i.GetType()))
                    {
                        jindu2[i] = (int)(Reader[i]);
                    }
                    else
                    {
                        jindu2[i] = (double)(Reader[i]);
                    }
                }
            }
            Reader.Close();


            //五分厂日进度
            Cmd.CommandText = sql.Replace("@d0", "'" + d1.ToShortDateString() + "'");
            Cmd.CommandText = Cmd.CommandText.Replace("@d0", "'" + d1.ToShortDateString() + "'");
            Cmd.CommandText = Cmd.CommandText.Replace("@fc", "5");
            Cmd.CommandType = CommandType.Text;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                d2 = (DateTime)Reader["月累计日期"];
                d3 = (DateTime)Reader["年累计日期"];
            }
            else
            {
                d2 = DateTime.Parse(d1.Year.ToString() + "年" + d1.Month.ToString() + "月1日");
                d3 = DateTime.Parse(d1.Year.ToString() + "年1月1日");
            }
            Reader.Close();
            ds1 = "'" + d1.ToShortDateString() + "'";
            ds2 = "'" + d2.ToShortDateString() + "'";
            ds3 = "'" + d3.ToShortDateString() + "'";

            Cmd.CommandText =
@"select * from
(select
ISNULL(SUM(车丝1),0) as 车丝1,
ISNULL(SUM(车丝2),0) as 车丝2,
ISNULL(SUM(车丝3),0) as 车丝3,
ISNULL(SUM(车丝4),0) as 车丝4
from 生产进度 where 分厂ID=5 and 日期=@d1
) t1
,
(select
ISNULL(SUM(车丝1),0) as 车丝1累计,
ISNULL(SUM(车丝2),0) as 车丝2累计,
ISNULL(SUM(车丝3),0) as 车丝3累计,
ISNULL(SUM(车丝4),0) as 车丝4累计
from 生产进度 where 分厂ID=5 and 日期>=@d2 and 日期<=@d1
) t2
,
(select
ISNULL(SUM(称测支数),0) as 油管称重测长支,
ISNULL(SUM(包装捆数),0) as 油管精整捆,
ISNULL(SUM(入库捆数),0) as 油管入库捆,
ISNULL(SUM(称测重量),0) as 油管称重测长kg,
ISNULL(SUM(包装重量),0) as 油管精整kg,
ISNULL(SUM(入库重量),0) as 油管入库kg
from 生产进度 where 分厂ID=5 and 产品类别ID=1 and 日期=@d1
) t3
,
(select
ISNULL(SUM(称测支数),0) as 油管称重测长累计支,
ISNULL(SUM(包装捆数),0) as 油管精整累计捆,
ISNULL(SUM(入库捆数),0) as 油管入库累计捆,
ISNULL(SUM(称测重量),0) as 油管称重测长累计kg,
ISNULL(SUM(包装重量),0) as 油管精整累计kg,
ISNULL(SUM(入库重量),0) as 油管入库累计kg
from 生产进度 where 分厂ID=5 and 产品类别ID=1 and 日期>=@d2 and 日期<=@d1
) t4
,
(select
ISNULL(SUM(称测支数),0) as 套管称重测长年累计支,
ISNULL(SUM(包装捆数),0) as 套管精整年累计捆,
ISNULL(SUM(入库捆数),0) as 套管入库年累计捆,
ISNULL(SUM(称测重量),0) as 套管称重测长年累计kg,
ISNULL(SUM(包装重量),0) as 套管精整年累计kg,
ISNULL(SUM(入库重量),0) as 套管入库年累计kg
from 生产进度 where 分厂ID=5 and 产品类别ID=1 and 日期>=@d3 and 日期<=@d1
) t5";
            Cmd.CommandText = Cmd.CommandText.Replace("@d1", ds1);
            Cmd.CommandText = Cmd.CommandText.Replace("@d2", ds2);
            Cmd.CommandText = Cmd.CommandText.Replace("@d3", ds3);

            Cmd.CommandType = CommandType.Text;
            Reader = Cmd.ExecuteReader();

            if (Reader.Read())
            {
                for (i = 0; i < Reader.FieldCount; i++)
                {
                    if (Reader[i].GetType().Equals(i.GetType()))
                    {
                        jindu5[i] = (int)(Reader[i]);
                    }
                    else
                    {
                        jindu5[i] = (double)(Reader[i]);
                    }
                }
            }
            Reader.Close();




            //三分厂日进度
            Cmd.CommandText = sql.Replace("@d0", "'" + d1.ToShortDateString() + "'");
            Cmd.CommandText = Cmd.CommandText.Replace("@d0", "'" + d1.ToShortDateString() + "'");
            Cmd.CommandText = Cmd.CommandText.Replace("@fc", "3");
            Cmd.CommandType = CommandType.Text;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                d2 = (DateTime)Reader["月累计日期"];
                d3 = (DateTime)Reader["年累计日期"];
            }
            else
            {
                d2 = DateTime.Parse(d1.Year.ToString() + "年" + d1.Month.ToString() + "月1日");
                d3 = DateTime.Parse(d1.Year.ToString() + "年1月1日");
            }
            Reader.Close();
            ds1 = "'" + d1.ToShortDateString() + "'";
            ds2 = "'" + d2.ToShortDateString() + "'";
            ds3 = "'" + d3.ToShortDateString() + "'";

            Cmd.CommandText =
@"select * from
(select
ISNULL(SUM(车丝1),0) as 车丝1,
ISNULL(SUM(车丝2),0) as 车丝2,
ISNULL(SUM(车丝3),0) as 车丝3,
ISNULL(SUM(车丝4),0) as 车丝4,
ISNULL(SUM(车丝5),0) as 车丝5,
ISNULL(SUM(车丝6),0) as 车丝6,
ISNULL(SUM(车丝7),0) as 车丝7,
ISNULL(SUM(车丝8),0) as 车丝8,
ISNULL(SUM(车丝9),0) as 车丝9,
ISNULL(SUM(车丝10),0) as 车丝10,
ISNULL(SUM(车丝11),0) as 车丝11,
ISNULL(SUM(车丝12),0) as 车丝12,
ISNULL(SUM(车丝13),0) as 车丝13,
ISNULL(SUM(车丝14),0) as 车丝14,
ISNULL(SUM(车丝15),0) as 车丝15,
ISNULL(SUM(车丝16),0) as 车丝16,
ISNULL(SUM(车丝17),0) as 车丝17,
ISNULL(SUM(车丝18),0) as 车丝18,
ISNULL(SUM(车丝19),0) as 车丝19,
ISNULL(SUM(车丝20),0) as 车丝20
from 生产进度3 where 日期=@d1
) t1
,
(select
ISNULL(SUM(车丝1),0) as 车丝1累计,
ISNULL(SUM(车丝2),0) as 车丝2累计,
ISNULL(SUM(车丝3),0) as 车丝3累计,
ISNULL(SUM(车丝4),0) as 车丝4累计,
ISNULL(SUM(车丝5),0) as 车丝5累计,
ISNULL(SUM(车丝6),0) as 车丝6累计,
ISNULL(SUM(车丝7),0) as 车丝7累计,
ISNULL(SUM(车丝8),0) as 车丝8累计,
ISNULL(SUM(车丝9),0) as 车丝9累计,
ISNULL(SUM(车丝10),0) as 车丝10累计,
ISNULL(SUM(车丝11),0) as 车丝11累计,
ISNULL(SUM(车丝12),0) as 车丝12累计,
ISNULL(SUM(车丝13),0) as 车丝13累计,
ISNULL(SUM(车丝14),0) as 车丝14累计,
ISNULL(SUM(车丝15),0) as 车丝15累计,
ISNULL(SUM(车丝16),0) as 车丝16累计,
ISNULL(SUM(车丝17),0) as 车丝17累计,
ISNULL(SUM(车丝18),0) as 车丝18累计,
ISNULL(SUM(车丝19),0) as 车丝19累计,
ISNULL(SUM(车丝20),0) as 车丝20累计
from 生产进度3 where 日期>=@d2 and 日期<=@d1
) t2
,
(select
ISNULL(SUM(车丝1),0) as 车丝1年累计,
ISNULL(SUM(车丝2),0) as 车丝2年累计,
ISNULL(SUM(车丝3),0) as 车丝3年累计,
ISNULL(SUM(车丝4),0) as 车丝4年累计,
ISNULL(SUM(车丝5),0) as 车丝5年累计,
ISNULL(SUM(车丝6),0) as 车丝6年累计,
ISNULL(SUM(车丝7),0) as 车丝7年累计,
ISNULL(SUM(车丝8),0) as 车丝8年累计,
ISNULL(SUM(车丝9),0) as 车丝9年累计,
ISNULL(SUM(车丝10),0) as 车丝10年累计,
ISNULL(SUM(车丝11),0) as 车丝11年累计,
ISNULL(SUM(车丝12),0) as 车丝12年累计,
ISNULL(SUM(车丝13),0) as 车丝13年累计,
ISNULL(SUM(车丝14),0) as 车丝14年累计,
ISNULL(SUM(车丝15),0) as 车丝15年累计,
ISNULL(SUM(车丝16),0) as 车丝16年累计,
ISNULL(SUM(车丝17),0) as 车丝17年累计,
ISNULL(SUM(车丝18),0) as 车丝18年累计,
ISNULL(SUM(车丝19),0) as 车丝19年累计,
ISNULL(SUM(车丝20),0) as 车丝20年累计
from 生产进度3 where 日期>=@d3 and 日期<=@d1
) t3
,
(select
ISNULL(SUM(磷化支数),0) as 套管接箍入库支
from 生产进度3 where 日期=@d1 and 产品类别ID=2
)
t4
,
(select
ISNULL(SUM(磷化支数),0) as 套管接箍入库累计支
from 生产进度3 where 日期>=@d2 and 日期<=@d1 and 产品类别ID=2
)
t5
,
(select
ISNULL(SUM(磷化支数),0) as 套管接箍入库年累计支
from 生产进度3 where 日期>=@d3 and 日期<=@d1 and 产品类别ID=2
)
t6
,
(select
ISNULL(SUM(磷化支数),0) as 油管接箍入库支
from 生产进度3 where 日期=@d1 and 产品类别ID=1
)
t7
,
(select
ISNULL(SUM(磷化支数),0) as 油管接箍入库累计支
from 生产进度3 where 日期>=@d2 and 日期<=@d1 and 产品类别ID=1
)
t8
,
(select
ISNULL(SUM(磷化支数),0) as 油管接箍入库年累计支
from 生产进度3 where 日期>=@d3 and 日期<=@d1 and 产品类别ID=1
)
t9";
            Cmd.CommandText = Cmd.CommandText.Replace("@d1", ds1);
            Cmd.CommandText = Cmd.CommandText.Replace("@d2", ds2);
            Cmd.CommandText = Cmd.CommandText.Replace("@d3", ds3);

            Cmd.CommandType = CommandType.Text;
            Reader = Cmd.ExecuteReader();
 
            if (Reader.Read())
            {
                for (i = 0; i < Reader.FieldCount; i++)
                {
                    jindu3[i] = (int)(Reader[i]);
                }
            }
            Reader.Close();


            //四分厂日进度
            Cmd.CommandText = sql.Replace("@d0", "'" + d1.ToShortDateString() + "'");
            Cmd.CommandText = Cmd.CommandText.Replace("@d0", "'" + d1.ToShortDateString() + "'");
            Cmd.CommandText = Cmd.CommandText.Replace("@fc", "4");
            Cmd.CommandType = CommandType.Text;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                d2 = (DateTime)Reader["月累计日期"];
                d3 = (DateTime)Reader["年累计日期"];
            }
            else
            {
                d2 = DateTime.Parse(d1.Year.ToString() + "年" + d1.Month.ToString() + "月1日");
                d3 = DateTime.Parse(d1.Year.ToString() + "年1月1日");
            }
            Reader.Close();
            ds1 = "'" + d1.ToShortDateString() + "'";
            ds2 = "'" + d2.ToShortDateString() + "'";
            ds3 = "'" + d3.ToShortDateString() + "'";

            Cmd.CommandText =
@"select * from
(select
ISNULL(SUM(车丝1),0) as 车丝1,
ISNULL(SUM(车丝2),0) as 车丝2,
ISNULL(SUM(车丝3),0) as 车丝3,
ISNULL(SUM(车丝4),0) as 车丝4
from 生产进度4 where 日期=@d1
) t1
,
(select
ISNULL(SUM(车丝1),0) as 车丝1累计,
ISNULL(SUM(车丝2),0) as 车丝2累计,
ISNULL(SUM(车丝3),0) as 车丝3累计,
ISNULL(SUM(车丝4),0) as 车丝4累计
from 生产进度4 where 日期>=@d2 and 日期<=@d1
) t2
,
(select
ISNULL(SUM(车丝1),0) as 车丝1年累计,
ISNULL(SUM(车丝2),0) as 车丝2年累计,
ISNULL(SUM(车丝3),0) as 车丝3年累计,
ISNULL(SUM(车丝4),0) as 车丝4年累计
from 生产进度4 where 日期>=@d3 and 日期<=@d1
) t3
,
(select
ISNULL(SUM(入库),0) as 套管保护器入库
from 生产进度4 where 产品类别ID=2 and 日期=@d1
) t4
,
(select
ISNULL(SUM(入库),0) as 套管保护器入库累计
from 生产进度4 where 产品类别ID=2 and 日期>=@d2 and 日期<=@d1
) t5
,
(select
ISNULL(SUM(入库),0) as 套管保护器入库年累计
from 生产进度4 where 产品类别ID=2 and 日期>=@d3 and 日期<=@d1
) t6

";
            Cmd.CommandText = Cmd.CommandText.Replace("@d1", ds1);
            Cmd.CommandText = Cmd.CommandText.Replace("@d2", ds2);
            Cmd.CommandText = Cmd.CommandText.Replace("@d3", ds3);

            Cmd.CommandType = CommandType.Text;
            Reader = Cmd.ExecuteReader();

            if (Reader.Read())
            {
                for (i = 0; i < Reader.FieldCount; i++)
                {
                    for (i = 0; i < Reader.FieldCount; i++)
                    {
                        jindu4[i] = (int)(Reader[i]);
                    }
                }
            }
            Reader.Close();

            Conn.Close();
            Reader.Dispose();
            Cmd.Dispose();
            Conn.Dispose();

        }

        protected void Button_Export_Click(object sender, EventArgs e)
        {
            InitializeWorkbook();
            this.QueryData();
            HSSFSheet sheet1 = hssfworkbook.GetSheet("数据");
            int r = 0;
            int i;
            HSSFRow row;
            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("日期");
            row.CreateCell(1).SetCellValue(this.RiQi.Text);

            string op = (string)this.Session["OperatorName"];
            if (op == null)
            {
                op = "";
            }
            row = sheet1.CreateRow(r++);
            row.CreateCell(0).SetCellValue("制表人");
            row.CreateCell(1).SetCellValue(op);

            //导出一分厂进度
            string[] field1 = { 
"F1车丝1",
"F1车丝2",
"F1车丝3",
"F1车丝4",
"F1车丝1累计",
"F1车丝2累计",
"F1车丝3累计",
"F1车丝4累计",

"F1套管称重测长支",
"F1套管精整捆",
"F1套管入库捆",
"F1套管称重测长kg",
"F1套管精整kg",
"F1套管入库kg",

"F1套管称重测长累计支",
"F1套管精整累计捆",
"F1套管入库累计捆",
"F1套管称重测长累计kg",
"F1套管精整累计kg",
"F1套管入库累计kg",

"F1套管称重测长年累计支",
"F1套管精整年累计捆",
"F1套管入库年累计捆",
"F1套管称重测长年累计kg",
"F1套管精整年累计kg",
"F1套管入库年累计kg"

                             };
            for (i = 0; i < field1.Length; i++)
            {
                row = sheet1.CreateRow(r++);
                row.CreateCell(0).SetCellValue(field1[i]);
                row.CreateCell(1).SetCellValue(jindu1[i]);
            }



            //导出二分厂进度
            string[] field2 = { 
"F2车丝1",
"F2车丝2",
"F2车丝3",
"F2车丝4",
"F2车丝5",
"F2车丝6",
"F2车丝1累计",
"F2车丝2累计",
"F2车丝3累计",
"F2车丝4累计",
"F2车丝5累计",
"F2车丝6累计",


"F2套管称重测长支",
"F2套管精整捆",
"F2套管入库捆",
"F2套管称重测长kg",
"F2套管精整kg",
"F2套管入库kg",

"F2套管称重测长累计支",
"F2套管精整累计捆",
"F2套管入库累计捆",
"F2套管称重测长累计kg",
"F2套管精整累计kg",
"F2套管入库累计kg",

"F2套管称重测长年累计支",
"F2套管精整年累计捆",
"F2套管入库年累计捆",
"F2套管称重测长年累计kg",
"F2套管精整年累计kg",
"F2套管入库年累计kg",


"F2油管称重测长支",
"F2油管精整捆",
"F2油管入库捆",
"F2油管称重测长kg",
"F2油管精整kg",
"F2油管入库kg",

"F2油管称重测长累计支",
"F2油管精整累计捆",
"F2油管入库累计捆",
"F2油管称重测长累计kg",
"F2油管精整累计kg",
"F2油管入库累计kg",

"F2油管称重测长年累计支",
"F2油管精整年累计捆",
"F2油管入库年累计捆",
"F2油管称重测长年累计kg",
"F2油管精整年累计kg",
"F2油管入库年累计kg"

                             };
            for (i = 0; i < field2.Length; i++)
            {
                row = sheet1.CreateRow(r++);
                row.CreateCell(0).SetCellValue(field2[i]);
                row.CreateCell(1).SetCellValue(jindu2[i]);
            }


            //导出三分厂进度
            string[] field3 = { 
"F3车丝1",
"F3车丝2",
"F3车丝3",
"F3车丝4",
"F3车丝5",
"F3车丝6",
"F3车丝7",
"F3车丝8",
"F3车丝9",
"F3车丝10",
"F3车丝11",
"F3车丝12",
"F3车丝13",
"F3车丝14",
"F3车丝15",
"F3车丝16",
"F3车丝17",
"F3车丝18",
"F3车丝19",
"F3车丝20",
"F3车丝1累计",
"F3车丝2累计",
"F3车丝3累计",
"F3车丝4累计",
"F3车丝5累计",
"F3车丝6累计",
"F3车丝7累计",
"F3车丝8累计",
"F3车丝9累计",
"F3车丝10累计",
"F3车丝11累计",
"F3车丝12累计",
"F3车丝13累计",
"F3车丝14累计",
"F3车丝15累计",
"F3车丝16累计",
"F3车丝17累计",
"F3车丝18累计",
"F3车丝19累计",
"F3车丝20累计",

"F3车丝1年累计",
"F3车丝2年累计",
"F3车丝3年累计",
"F3车丝4年累计",
"F3车丝5年累计",
"F3车丝6年累计",
"F3车丝7年累计",
"F3车丝8年累计",
"F3车丝9年累计",
"F3车丝10年累计",
"F3车丝11年累计",
"F3车丝12年累计",
"F3车丝13年累计",
"F3车丝14年累计",
"F3车丝15年累计",
"F3车丝16年累计",
"F3车丝17年累计",
"F3车丝18年累计",
"F3车丝19年累计",
"F3车丝20年累计",

"F3套管接箍入库",
"F3套管接箍入库累计",
"F3套管接箍入库年累计",

"F3油管接箍入库",
"F3油管接箍入库累计",
"F3油管接箍入库年累计"



                             };
            for (i = 0; i < field3.Length; i++)
            {
                row = sheet1.CreateRow(r++);
                row.CreateCell(0).SetCellValue(field3[i]);
                row.CreateCell(1).SetCellValue(jindu3[i]);
            }


            //导出四分厂进度
            string[] field4 = { 
"F4车丝1",
"F4车丝2",
"F4车丝3",
"F4车丝4",

"F4车丝1累计",
"F4车丝2累计",
"F4车丝3累计",
"F4车丝4累计",

"F4车丝1年累计",
"F4车丝2年累计",
"F4车丝3年累计",
"F4车丝4年累计",

"F4套管保护器入库",
"F4套管保护器入库累计",
"F4套管保护器入库年累计"

                             };
            for (i = 0; i < field4.Length; i++)
            {
                row = sheet1.CreateRow(r++);
                row.CreateCell(0).SetCellValue(field4[i]);
                row.CreateCell(1).SetCellValue(jindu4[i]);
            }


            //导出五分厂进度
            string[] field5 = { 
"F5车丝1",
"F5车丝2",
"F5车丝3",
"F5车丝4",
"F5车丝1累计",
"F5车丝2累计",
"F5车丝3累计",
"F5车丝4累计",

"F5油管称重测长支",
"F5油管精整捆",
"F5油管入库捆",
"F5油管称重测长kg",
"F5油管精整kg",
"F5油管入库kg",

"F5油管称重测长累计支",
"F5油管精整累计捆",
"F5油管入库累计捆",
"F5油管称重测长累计kg",
"F5油管精整累计kg",
"F5油管入库累计kg",

"F5油管称重测长年累计支",
"F5油管精整年累计捆",
"F5油管入库年累计捆",
"F5油管称重测长年累计kg",
"F5油管精整年累计kg",
"F5油管入库年累计kg"

                             };
            for (i = 0; i < field5.Length; i++)
            {
                row = sheet1.CreateRow(r++);
                row.CreateCell(0).SetCellValue(field5[i]);
                row.CreateCell(1).SetCellValue(jindu5[i]);
            }




            HSSFSheet sheet2 = hssfworkbook.GetSheet("报表");
            sheet2.ForceFormulaRecalculation = true;
            WriteToFile();
            Response.End();
        }

    }
}
